package com.example.mysql.学习笔记.高级.数据库的优化之索引.索引优化与查询优化;

public class 排序优化 {
}
/**
 * 5.1排序优化
 * 问题:在WHERE条件字段上加索引，但是为什么在 ORDER BY (排序)字段上还要加索引呢?
 * 在MySQL中,支持两种排序方式，分别是 FileSort 和 Index 排序。
 * ● Index排序中，索引可以保证数据的有序性 ，不需要再进行排序，效率更高。
 * ● FileSort 排序则一般在内存中进行排序，占用CPU较多。
 *   如果待排结果较大，会产生临时文件I/O到磁盘进行排序的情况，效率较低。
 *
 * 优化建议:
 * 1. SQL 中，可以在WHERE子句和ORDER BY子句中使用索引，目的是在WHERE子句中避免全表扫描，
 *    在ORDER BY子句避免使用 FileSort 排序。
 *    当然，某些情况下全表扫描，或者FileSort 排序不一定比索引慢。
 *    但总的来说，我们还是要避免，以提高查询效率。
 * 2. 尽量使用Index完成ORDER BY排序。如果WHERE和ORDER BY后面是相同的列就使用单索引列;
 *    如果就使用联合索引。
 * 3. 无法使用Index时，需要对FileSort方式进行调优。
 *
 *
 * #过程二: order by时不limit，索引失效
 * #创建索引
 * CREATE INDEX idx_age_classid_name ON student (age, classid, name) ;
 *
 * #不限制，索引失效   因为不是主键索引  而且是 * from
 * 虽然索引时排好序的，但是由于需要查询所有列，所以需要全部回表操作，效率低于 全查出来在排序
 * EXPLAIN SELECT SQL_NO_CACHE *FROM student ORDER BY age, classid;
 * #用到了索引，因为所需要的列都在索引上，索引都有数据，不需要回表
 * EXPLAIN SELECT SQL_NO_CACHE age, classid FROM student ORDER BY age, classid;|
 *
 * 过程五:无过滤，不索引
 * EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid;
 *    //会发现  这个联合索引只用到了 age，因为在where条件过滤完后，如果就剩了几万条或更少，对电脑来说很少，就自己排序了
 * EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid, NAME;  //同上
 * EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age;//没用到组合索引 因为最左前缀原则
 * EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 10;
 * //用到了索引  因为只取前十条，他会直接从联合索引中查找 classid=45 的 只取前10个 这个效率高
 *
 *
 * 所有的排序都是在条件过滤之后才执行的。所以,如果条件过滤掉大部分数据的话，
 * 剩下几百几千条数据进行排其实并不是很消耗性能，即使索引优化了排序，但实际提升性能很有限。
 * 相对的 stuno<101000这个条件,如果没有用到索引的话，要对几万条的数据进行扫描,这是非常消耗性能的,
 * 所以索引放在这个字段上性价比最高，是最优选择。
 *
 * 1.两个索引同时存在，mysql自动选择最优的方案。(对于这个例子，mysql选择idx_ age. stuno name)
 *    但是，随着数据量的变化，选择的索引也会随之变化的。
 * 2. 当[范围条件]和[group by或者order by]的字段出现二选一时，优先观察条件字段的过滤数量，
 *   如果过滤的数据足够多]，而需要排序的数据并不多时，优先把索引放在范围字段上。反之，亦然。
 *
 *
 * filesort算法:双路排序和单路排序
 * 双路排序(慢)
 * ●MySQL 4.1之前是使用双路排序，字面意思就是两次扫描磁盘,最终得到数据，读取行指针和order by列,
 *  对他们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出
 * ●从磁盘取排序字段，在buffer进行排序, 再从磁盘取其他字段。
 *  取一批数据，要对磁盘进行两次扫描，众所周知，10是很耗时的，所以在mysql4.1之后，
 *  出现了第二种改进的算法，就是单路排序。
 * 单路排序(快)  直接把order by列全部取出来，之后在进行排序  不需要第二次扫描 ，但对缓存要求高
 * 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
 * 它的效率更快一些, 避免了第二次读取数据。
 * 并且把随机I0变成了顺序I0,但是它会使用更多的空间，因为它把每一行都保存在内存中了。
 *
 *
 * 结论及引申出的问题
 * ●由于单路是后出的，总体而言好过双路
 * ●但是用单路有问题
 *  。在sort_buffer中， 单路比多路要多占用很多空间，因为单路是把所有字段都取出，所以有可能取出的数据的
 *  总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，
 *  进行排序(创建tmp文件，多路合并)，排完再取sort_ buffer容量大小，再排.... 从而多次/0。
 *  单路本来想省--次I/0操作, 反而导致了大量的I/0操作 ，反而得不偿失。
 *
 *  优化策略
 * 1.尝试提高sort_buffer_size
 * InnoDB存储引擎默认值是1048576字节，1MB。
 * 2.尝试提高max_length_forsort_ data
 * ●提高这个参数，会增加用改进算法的概率。
 * SHOW VARIABLES LIKE ' %max_length_for_sort_data%' ;
 * #默认1024字节
 * ●但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大,明显症状是高的磁盘I/0活动和低的处理
 * 器使用率。如果需要返回的列的总长度大于max_ length_ for. sort _data, 使用双路算法，否则使用单路算法。
 * 1024-8192字节之间调整
 *
 * 3. Order by时select*是-个大忌。最好只Query需要的字段。原因:
 * ●当Query的字段大小总和小于max_length_for_sort_data，而且排序字段不是TEXT|BLOB类型时，会用改
 * 进后的算法--单路排序， 否则用老算法--多路排序。
 * ●两种算法的数据都有可能超出sort_buffer_size的容量,超出之后，会创建tmp文件进行合并排序，导致多次
 * 1/0,但是用单路排序算法的风险会更大--些， 所以要提高sort_buffer_size 。
 *
 *
 */
